﻿using aspNetCURD.Database;
using aspNetCURD.Dtos;
using aspNetCURD.Help;
using aspNetCURD.Models;
using aspNetCURD.Services;
using AutoMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient.Server;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Transactions;

namespace aspNetCURD.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    [Authorize]
    public class StudentController : ControllerBase
    {
        private readonly IStudentRepository _studentRepository;
        private readonly AppDbContext _context;
        private readonly IMapper _mapper;
        public StudentController(IStudentRepository studentRepository, AppDbContext context, IMapper mapper)
        {
            _studentRepository = studentRepository;
            _context = context;
            _mapper = mapper;
        }
        /// <summary>
        /// 获取学生信息，可关键字模糊查询匹配
        /// </summary>
        /// <param name="keyword">搜索字符串</param>
        /// <returns>学生信息列表</returns>
        [HttpGet]
        public IActionResult GetStudents([FromQuery] string keyword, int pageIndex, int pageSize)
        {
            var studentRepository = _studentRepository.GetStudents(keyword, pageIndex, pageSize);
            if (studentRepository == null || studentRepository.Count() <= 0)
            {
                return NotFound("没有找到学生信息");
            }
            return Ok(studentRepository);
        }

        /// <summary>
        /// 获取学生信息条数
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        [HttpGet("StudentsCount")]
        public IActionResult GetStudentsCount([FromQuery] string keyword)
        {
            var studentContRepository = _studentRepository.GetStudentsCount(keyword);
            if (studentContRepository <= 0)
            {
                return NotFound("没有找到学生信息");
            }
            return Ok(studentContRepository);
        }

        /// <summary>
        /// 根据 id获取学生信息
        /// </summary>
        /// <param name="sno">学号</param>
        /// <returns>学生信息</returns>
        [HttpGet("{sno}")]
        public IActionResult GetStudent(string sno)
        {
            if (string.IsNullOrEmpty(sno))
            {
                return BadRequest("学号不能为空");
            }
            var studentRepository = _studentRepository.GetStudent(sno);
            if (studentRepository == null)
            {
                return NotFound("没有找到该学生信息");
            }
            return Ok(studentRepository);
        }
        /// <summary>
        /// 新增学生信息
        /// </summary>
        /// <param name="studentDto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult CreateStudent([FromBody] StudentCreationDto studentDto)
        {
            if (studentDto == null)
            {
                return BadRequest("输入为空或请求语法错误");
            }
            using(var context = _context)
            {
                var studentModel = _mapper.Map<Student>(studentDto);
                try
                {
                    _studentRepository.AddStudent(studentModel);
                    _studentRepository.Save();
                }
                catch (Exception ex)
                {
                    return StatusCode(500, ex.Message);
                }
            }
            return Ok("新增学生信息成功");
        }
        /// <summary>
        /// put方法修改学生信息
        /// </summary>
        /// <param name="sno">学号</param>
        /// <param name="student">学生信息</param>
        /// <returns>修改成功信息</returns>
        [HttpPut]
        public IActionResult UpdateStudent([FromQuery] string sno, [FromBody] Student student)
        {
            if (string.IsNullOrEmpty(sno) || student.sno.Trim() != sno.Trim())
            {
                return BadRequest("学号为空或输入错误");
            }
            // Entry() 方法来更新模型的状态，EntityState 的枚举值为模型被上下文跟踪的状态。
            _context.Entry(student).State = EntityState.Modified;
            try
            {
                _context.SaveChanges();
            }
            catch
            {
                if (_studentRepository.StudentEmpty(sno))
                {
                    return NotFound("未找到该学生信息");
                }
                else
                {
                    throw;
                }
            }
            return Ok("修改成功");
        }
        /// <summary>
        /// 删除学生信息
        /// </summary>
        /// <param name="sno"></param>
        /// <returns></returns>
        [HttpDelete]
        public IActionResult DeleteStudent([FromQuery] string sno)
        {
            if (string.IsNullOrEmpty(sno))
            {
                return BadRequest("输入为空或请求语法错误");
            }
            var studentRepository = _studentRepository.GetStudent(sno);
            if (studentRepository == null)
            {
                return BadRequest($"未找到该学生: {sno}");
            }
            _context.Entry(studentRepository).State = EntityState.Deleted;
            try
            {
                _context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return Ok("删除成功");
        }


        /// <summary>
        /// 上传 Excel文件
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        [HttpPost("Upload")]
        public IActionResult UploadExcel(IFormFile file)
        {
            if (file == null || file.Length == 0)
            {
                return BadRequest();
            }
            if (file.FileName.ToLower().EndsWith(".xlsx") || file.FileName.ToLower().EndsWith(".xls"))
            {
                var filePath = Path.Combine(@"E:\桌面", "Upload" + file.FileName);
                using (var str = new FileStream(filePath, FileMode.Create))
                {
                    file.CopyTo(str);
                }
                Console.WriteLine("22222222222222222222");
                Console.WriteLine(file.FileName);
                Console.WriteLine("333333333333333");
                Console.WriteLine(file.ContentType);
                Console.WriteLine("3333333333333");
                Console.WriteLine(file.Name);

                Console.WriteLine("22222222222222222222");
                using (var stream = new MemoryStream())
                {
                    file.CopyTo(stream);
                    stream.Position = 0;

                    using (var package = new ExcelPackage(stream))
                    {
                        // Excel 软件包许可
                        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                        // 创建一个 Excel文件，并创建一个 sheet表
                        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                        int rowCount = worksheet.Dimension.Rows;

                        List<Student> students = new List<Student>();
                        for (int row = 3; row <= rowCount; row++) // 假设第一行是标题行，数据从第二行开始
                        {
                            students.Add(new Student()
                            {
                                sno = worksheet.Cells[row, 1].Value?.ToString(),
                                sname = worksheet.Cells[row, 2].Value?.ToString(),
                                ssex = worksheet.Cells[row, 3].Value?.ToString(),
                                birthday = Convert.ToDateTime(worksheet.Cells[row, 4].Value?.ToString()),
                                en_time = Convert.ToDateTime(worksheet.Cells[row, 5].Value?.ToString()),
                                specialty = worksheet.Cells[row, 6].Value?.ToString(),
                                grade = worksheet.Cells[row, 7].Value?.ToString()
                            });
                        }
                        try
                        {
                            _context.student.AddRange(students);
                            _context.SaveChanges();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.ToString());
                        }
                        return Ok("上传成功");
                    }
                }
            }
            else
            {
                return BadRequest("只允许上传 xlsx 或 xls 格式的文件");
            }
        }

        /// <summary>
        /// Excel文件下载
        /// </summary>
        /// <returns></returns>
        [HttpGet("download")]
        public IActionResult DownloadExcel(int pageIndex, int pageSize)
        {
            List<Student> students = _studentRepository.GetStudents("", pageIndex, pageSize).ToList();

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage())
            {
                // 在 Excel 中创建一个 sheet表
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("newStudent");

                for (int i = 1; i <= students.Count(); i++)
                {
                    workSheet.Column(i).Width = 15;
                }

                workSheet.Cells[1, 1].Value = "学号";
                workSheet.Cells[1, 2].Value = "姓名";
                workSheet.Cells[1, 3].Value = "性别";
                workSheet.Cells[1, 4].Value = "生日";
                workSheet.Cells[1, 5].Value = "入学时间";
                workSheet.Cells[1, 6].Value = "专业";
                workSheet.Cells[1, 7].Value = "年级";

                //workSheet.Cells[2, 7].Merge = true;
                //workSheet.Cells[2, 7].Value = "学生信息表";




                for (int i = 1; i < students.Count(); i++)
                {
                    workSheet.Cells[2 + i, 1].Value = students[i].sno;
                    workSheet.Cells[2 + i, 2].Value = students[i].sname;
                    workSheet.Cells[2 + i, 3].Value = students[i].ssex;
                    workSheet.Cells[2 + i, 4].Value = students[i].birthday;
                    workSheet.Cells[2 + i, 5].Value = students[i].en_time;
                    workSheet.Cells[2 + i, 6].Value = students[i].specialty;
                    workSheet.Cells[2 + i, 7].Value = students[i].grade;
                }
                // 保存 Excel文件
                MemoryStream stream = new MemoryStream(package.GetAsByteArray());
                string fileName = "newStudent.XLSX";
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                return File(stream, contentType, fileName);
            }
        }
    }
}
